using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;
using iver.Entity;
using DBEntity;
using NPOI.HSSF.UserModel;
//using NPOI.HPSF;
//using NPOI.POIFS.FileSystem;
//using NPOI.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace iver.Controllers
{
    public class myTestController : ApiController
    {
        /// <summary>
        /// 得到列表
        /// </summary>
        [HttpPost]
        public ResResult myTestList([FromBody]ReqList req)
        {
            AuthInfo authInfo;
            if (!ReqBase.isAuth(out authInfo))
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailAuth,
                };
            }
            iverDB db = new iverDB();
            var list = (from u in db.sysRole
                        orderby u.id descending
                        select u);
            var arr = list.Skip(req.limit * (req.page - 1)).Take(req.limit);
            int count = list.Count();
            return new ResResult
            {
                success = true,
                result = new
                {
                    count,
                    arr,
                }
            };
        }
        
        
        /// <summary>
        /// 得到关联列表
        /// </summary>
        [HttpPost]
        public ResResult sysUserList([FromBody]ReqList req)
        {
            AuthInfo authInfo;
            if (!ReqBase.isAuth(out authInfo))
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailAuth,
                };
            }
            iverDB db = new iverDB();
            var list = (from u in db.sysUser
                        orderby u.id descending
                        select new {
                            u.id,
                            u.userName,
                        });
            var arr = list.Skip(req.limit * (req.page - 1)).Take(req.limit);
            int count = list.Count();
            return new ResResult
            {
                success = true,
                result = new
                {
                    count,
                    arr,
                }
            };
        }
        
        public class ReqmyTestList : ReqList
        {
			public string roleName { get; set; }
			public string remark { get; set; }
			public string createTime { get; set; }
			public Nullable<bool> isEnable { get; set; }
			public Nullable<int> createBy { get; set; }
			public string fuserName { get; set; }        
        }
        /// <summary>
        /// 得到列表通过条件
        /// </summary>
        [HttpPost]
        public ResResult myTestListByQuery([FromBody]ReqmyTestList req)
        {
            AuthInfo authInfo;
            if (!ReqBase.isAuth(out authInfo))
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailAuth,
                };
            }

            DateTime? b_createTime = null;
            DateTime? e_createTime = null;
            if (req.createTime != null)
            {
                string[] temp = req.createTime.Split(',');
                DateTime bt = Convert.ToDateTime(temp[0]);
                DateTime et = Convert.ToDateTime(temp[1]);
                b_createTime = new DateTime(bt.Year, bt.Month, bt.Day, 0, 0, 0);
                e_createTime = new DateTime(et.Year, et.Month, et.Day, 23, 59, 59);
            }
                      
            iverDB db = new iverDB();
            var list = (from u in db.sysRole
                        join jsysUser in db.sysUser on u.createBy equals jsysUser.id into tsysUser
                        from fsysUser in tsysUser.DefaultIfEmpty()
                        orderby u.id descending
                        where
                            1== 1 
						&& (req.roleName == null ? 1 == 1 : u.roleName.Contains(req.roleName))
						&& (req.remark == null ? 1 == 1 : u.remark.Contains(req.remark))
						&& (req.createTime == null ? 1 == 1 : (u.createTime >= b_createTime && u.createTime <= e_createTime))
						&& (req.isEnable == null ? 1 == 1 : u.isEnable == req.isEnable)
						&& (req.createBy == null ? 1 == 1 : u.createBy == req.createBy)
						&& (req.fuserName == null ? 1 == 1 : fsysUser.userName.Contains(req.fuserName))        
                        select new
                        {
                            u.id,
u.roleName,
u.remark,
u.createTime,
u.isEnable,
u.createBy,
                            fuserName = fsysUser.userName,
                        });
            var arr = list.Skip(req.limit * (req.page - 1)).Take(req.limit);
            int count = list.Count();
            return new ResResult
            {
                success = true,
                result = new
                {
                    count,
                    arr,
                }
            };
        }
        
        /// <summary>
        /// 添加
        /// </summary>
        [HttpPost]
        public ResResult myTestAdd([FromBody]sysRole req)
        {
            AuthInfo authInfo;
            if (!ReqBase.isAuth(out authInfo))
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailAuth,
                };
            }
            iverDB db = new iverDB();
            db.sysRole.Add(req);
            db.SaveChanges();
            Help.Log(authInfo, req.id.ToString());
            return new ResResult
            {
                success = true,
                rescode = ResCode.SuccessAdd,
                result = new
                {
                    id = req.id,
                },
            };
        }
        
        /// <summary>
        /// 编辑
        /// </summary>
        [HttpPost]
        public ResResult myTestEdit([FromBody]sysRole req)
        {
            AuthInfo authInfo;
            if (!ReqBase.isAuth(out authInfo))
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailAuth,
                };
            }
            iverDB db = new iverDB();
            sysRole model = db.sysRole.FirstOrDefault(u => u.id == req.id);
            if (model == null)
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailParams,
                };
            }
			model.roleName = req.roleName == null ? model.roleName : req.roleName;
			model.remark = req.remark == null ? model.remark : req.remark;
			model.createTime = req.createTime == null ? model.createTime : req.createTime;
			model.isEnable = req.isEnable == null ? model.isEnable : req.isEnable;
			model.createBy = req.createBy == null ? model.createBy : req.createBy;
            int result = db.SaveChanges();
            Help.Log(authInfo, req.id.ToString());
            return new ResResult
            {
                success = true,
                rescode = ResCode.SuccessEdit,
                result = new
                {
                    changeCount = result,
                },
            };
        }
        
        /// <summary>
        /// 删除
        /// </summary>
        [HttpPost]
        public ResResult myTestDel([FromBody]ReqID<List<int>> req)
        {
            AuthInfo authInfo;
            if (!ReqBase.isAuth(out authInfo))
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailAuth,
                };
            }
            iverDB db = new iverDB();
            var list = db.sysRole.Where(u => req.id.Contains(u.id));
            int delCount = list.Count();
            if (delCount == 0)
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailParams,
                };
            }
            db.sysRole.RemoveRange(list);
            db.SaveChanges();
            Help.Log(authInfo, Newtonsoft.Json.JsonConvert.SerializeObject(req.id));
            return new ResResult
            {
                success = true,
                rescode = ResCode.SuccessDel,
                result = new
                {
                    delCount,
                },
            };
        }
        
        /// <summary>
        /// excel导入参数
        /// </summary>
        public class ReqImport
        {
            public string label { get; set; }
            public string value { get; set; }
            public string type { get; set; }
        }
        /// <summary>
        /// excel导入
        /// </summary>
        [HttpPost]
        public ResResult importExcel()
        {

            AuthInfo authInfo;
            if (!ReqBase.isAuth(out authInfo))
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailAuth,
                };
            }
            if (System.Web.HttpContext.Current.Request["jsonStr"] == null)
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailParams,
                    result = "无导入参数",

                };
            }
            IWorkbook workbook;
            string fileExt = System.IO.Path.GetExtension(System.Web.HttpContext.Current.Request.Files[0].FileName);
            if (!(fileExt.ToLower() == ".xls" || fileExt.ToLower() == ".xlsx"))
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailParams,
                    result = "文件格式错误",

                };
            }

            string jsonStr = System.Web.HttpContext.Current.Request["jsonStr"];
            System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer();
            List<ReqImport> mylist = js.Deserialize<List<ReqImport>>(jsonStr);
            List<ReqImport> oldlist = new List<ReqImport>();
            
            oldlist.Add(new ReqImport()
            {
                value = "roleName",
                type = "varchar",
            });
            oldlist.Add(new ReqImport()
            {
                value = "remark",
                type = "varchar",
            });
            oldlist.Add(new ReqImport()
            {
                value = "createTime",
                type = "datetime",
            });
            oldlist.Add(new ReqImport()
            {
                value = "isEnable",
                type = "bit",
            });
            oldlist.Add(new ReqImport()
            {
                value = "createBy",
                type = "int",
            });
            List<ReqImport> dbList = new List<ReqImport>();
            for (int i = 0; i < oldlist.Count; i++)
            {
                for (int j = 0; j < mylist.Count; j++)
                {
                    if (mylist[j].value == oldlist[i].value)
                    {

                        dbList.Add(new ReqImport()
                        {
                            label = mylist[j].label,
                            value = oldlist[i].value,
                            type = oldlist[i].type,
                        });
                        break;
                    }
                }
            }

            if (fileExt == ".xls")
            {
                workbook = new HSSFWorkbook(System.Web.HttpContext.Current.Request.Files[0].InputStream);
            }
            else
            {
                workbook = new XSSFWorkbook(System.Web.HttpContext.Current.Request.Files[0].InputStream);
            }
            ISheet _sheet = workbook.GetSheetAt(0);
            List<ReqImport> flist = new List<ReqImport>();
            bool flag = false;
            for (int i = 0; i < _sheet.GetRow(0).Cells.Count; i++)
            {
                bool flaghas = false;
                for (int j = 0; j < dbList.Count; j++)
                {
                    if (dbList[j].label == _sheet.GetRow(0).Cells[i].ToString())
                    {
                        flaghas = true;
                        flist.Add(dbList[j]);
                        break;
                    }
                }
                if (!flaghas)
                {
                    flag = true;
                }
            }
            if (flag)
            {
                return new ResResult
                {
                    success = false,
                    rescode = ResCode.FailParams,
                    result = "导入格式错误",
                };
            }
            ////数组去重
            //for (int i = 0; i < flist.Count; i++)
            //{
            //    List<ReqImport> ll = flist.FindAll(p => p.value == flist[i].value);
            //    if (ll.Count > 1)
            //    {
            //        flist.Remove(ll[0]);
            //    }
            //}
            List<ReqImport> finalList = flist;
            List<sysRole> list = new List<sysRole>();
            for (int i = _sheet.LastRowNum ; i > 0; i--)
            {
                sysRole model = new sysRole();
                IRow row = _sheet.GetRow(i);
                IRow typeRow = _sheet.GetRow(0);
                for (int j = 0; j < row.LastCellNum; j++)
                {
                    List<ReqImport> rs = finalList.FindAll(p => p.label == typeRow.Cells[j].ToString());
                    string cellVal = row.Cells[j].ToString();
                    switch (rs[0].type)
                    {
                        case "int":
                            int int_v;
                            if (int.TryParse(cellVal, out int_v))
                            {
                                model.GetType().GetProperty(rs[0].value).SetValue(model, int_v);
                            }
                            break;
                        case "datetime":
                            DateTime DateTime_v;
                            if (DateTime.TryParse(cellVal, out DateTime_v))
                            {
                                model.GetType().GetProperty(rs[0].value).SetValue(model, DateTime_v);
                            }
                            break;
                        case "bit":
                            bool bool_v;
                            if (bool.TryParse(cellVal, out bool_v))
                            {
                                model.GetType().GetProperty(rs[0].value).SetValue(model, bool_v);
                            }
                            break;
                        default:
                            model.GetType().GetProperty(rs[0].value).SetValue(model, cellVal);
                            break;
                    }
                }
                list.Add(model);
            }
            iverDB db = new iverDB();
            db.sysRole.AddRange(list);
            int count = db.SaveChanges();
            Help.Log(authInfo, count.ToString());

            return new ResResult
            {
                success = true,
                result = finalList,
            };

        }
        
        
        
    }
}
  